﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SqlCommandHelper;//Must to be Referece
using System.Data;
using Vergnaty.Repository.Model;

namespace Vergnaty.Repository
{
    //Example of how to use
    public class UserRepository : IRepository<User>
    {
        #region const
        const string ADD_USER = "[dbo].[User_Insert]";
        const string UPDATE_USER = "[dbo].[User_Update]";
        const string GET_BY_FILTER = "[dbo].[User_GetByFilter]";
        const string GET_BY_ID = "[dbo].[User_GetById]";
        const string DELETE = "[dbo].[User_Delete]";
        #endregion

        string _connectionString;

        public UserRepository(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException("connectionString", "cannot be null or empty");

            _connectionString = connectionString;
        }
        public User Add(User entity)
        {
            if (entity == null)
                throw new ArgumentNullException("entity", "cannot be null");
            
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                //Open connection
                connection.Open();
                
                //Create a new sql command
                SqlCommand command = new SqlCommand(ADD_USER, connection);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                //Call sql command helper to map all properties into SqlParameters
                command.SetParameters(entity).Build();
                command.ExecuteNonQuery();
            }
            return entity;
        }

        public User GetById(Guid id)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                //Open connection
                connection.Open();
                
                SqlCommand command = new SqlCommand(GET_BY_ID, connection);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                command.SetParameter("@Id", DbType.Guid, ParameterDirection.Input, id);

                User user = null;
                using (IDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        user = reader.MapToEntity<User>();
                    }
                }
                return user;
            }
        }

        public User Update(User entity)
        {

            if (entity == null)
                throw new ArgumentNullException("entity", "cannot be null");

            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                //Open connection
                connection.Open();

                //Create a new sql command
                SqlCommand command = new SqlCommand(UPDATE_USER, connection);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                //Call sql command helper to map all properties into SqlParameters
                command.SetParameters(entity).Build();
                command.ExecuteNonQuery();
            }
            return entity;
        }

        public void Delete(Guid id)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                
                SqlCommand command = new SqlCommand(DELETE, connection);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                command.SetParameter("@Id", DbType.Guid, ParameterDirection.Input, id);
                command.ExecuteNonQuery();
            }
        }

        public IList<User> GetList(User filter)
        {
            if (filter == null)
                throw new ArgumentNullException("entity", "cannot be null");

            IList<User> users = new List<User>();
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                //Open connection
                connection.Open();

                //Create a new sql command
                SqlCommand command = new SqlCommand(GET_BY_FILTER, connection);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                //Call sql command helper to map to properties of filter(FirstName,Age)
                command.SetParameters(filter).Parameters(c=> new {
                    c.FistName,
                    c.LastName,
                    c.Age
                });

              
                using(IDataReader reader = command.ExecuteReader())
                {
                    users = reader.MapToEntityList<User>();
                }
            }
            return users;
        }
    }
}
